﻿using DbJobsApi.Dto;
using DbJobsApi.Dto.Sqlite;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.Sqlite;
using DbJobsApi.Extensions;
using System.Data;

namespace DbJobsApi.Controllers
{
    [Route("/api/[controller]/[action]")]
    [ApiController]
    public class SqliteController : ControllerBase
    {
        private readonly ILogger<SqliteController> logger;
        public SqliteController(ILogger<SqliteController> logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 获取指定库的所有表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public PageResult<CustomTableEntity> GetTables(SearchTableEntity entity)
        {
            using var conn = new SqliteConnection(entity.DbConnStr);

            var list = conn.SqliteQuery<CustomTableEntity>(
                @"SELECT name TABLE_NAME, UPPER(type) TABLE_TYPE FROM sqlite_master WHERE type in ('table', 'view')");

            if (list == null)
                list = new();

            var whereList = list.AsQueryable();
            if (!string.IsNullOrEmpty(entity.TABLE_NAME))
            {
                whereList = whereList.Where(m => m.TABLE_NAME.Contains(entity.TABLE_NAME));
            }

            var records = list.Count;
            var result = new PageResult<CustomTableEntity>
            {
                rows = whereList
                        .OrderBy(entity.sidx, entity.sord)
                        .Skip((entity.page - 1) * entity.rows)
                        .Take(entity.rows).ToList(),
                records = records,
                page = entity.page,
                total = records / entity.rows + (records % entity.rows != 0 ? 1 : 0)
            };
            return result;
        }


        public class _TEMP
        {
            public string name { get; set; }
            public string type { get; set; }
            public int notnull { get; set; }
            public object dflt_value { get; set; }
            public int pk { get; set; }
        }
        /// <summary>
        /// 获取指定表的结构信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        [HttpPost]
        public List<TableColumnEntity> GetTable(GetTableEntity entity)
        {
            using var conn = new SqliteConnection(entity.DbConnStr);
            List<TableColumnEntity> result = new ();
            try
            {

                var rows = conn.SqliteQuery<_TEMP>($"PRAGMA table_info('{entity.TABLE_NAME}')");
               
                if(rows == null || rows.Count == 0)
                {
                    return result;
                }

                foreach(var row in rows)
                {
                    var arr = row.type.Split('(', ',', ')');
                    var obj = new TableColumnEntity
                    {
                        ISKEY = row.pk,
                        COLUMN_NAME = row.name,
                        DATA_TYPE = arr[0].ToLower(),
                        DATA_LENGTH = arr.Length > 1 ? int.Parse(arr[1]): null,
                        DATA_SCALE = arr.Length == 4 && arr[0].ToLower() == "real" ? int.Parse(arr[2]) : null,
                        DATA_DEFAULT = row.dflt_value?.ToString(),
                        NULLABLE = row.notnull == 0 ? "Y" : "N", 
                    };
                    result.Add(obj);
                }

            }
            catch (Exception ex)
            {
                logger.LogInformation($"{ex.Message}");
                result = new List<TableColumnEntity>();
            }
            return result;
        }


        /// <summary>
        /// 编辑表结构定义 //此方法存在注入风险
        /// </summary>
        /// <param name="entity"></param>
        [HttpPost]
        public FlagEntity Edit(CustomTableEntity entity)
        {
            using var conn = new SqliteConnection(entity.DbConnStr);
            try
            {
                var columns = GetTable(new()
                { 
                    DbConnStr = entity.DbConnStr,
                    DbName = entity.DbName,
                    TABLE_NAME = entity.EDIT_TABLE_NAME 
                });

                var colSql = entity.Columns.Select(m =>
                {
                    if (m.ISKEY == 1 && m.AUTOIN == 1)
                        return $"{m.COLUMN_NAME_SQL} PRIMARY KEY AUTOINCREMENT";
                    else
                        return m.COLUMN_NAME_SQL;
                }).ToList();
                if(entity.Columns.Count(m => m.ISKEY == 1) > 1)
                {
                    colSql.Add($"PRIMARY KEY( {string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => $"\"{m.COLUMN_NAME}\""))}");
                }

                //建表
                var createTableSql = $"CREATE TABLE \"{entity.TABLE_NAME}\" ( {Environment.NewLine}{string.Join(",", colSql)} {Environment.NewLine});";
                if (columns.Count == 0)
                {
                    conn.SqliteExcute(createTableSql);
                }
                else
                {
                    var oldname = $"_{entity.EDIT_TABLE_NAME}_{DateTime.Now:yyyyMMddHHmmss}";
                    //先更名
                    var renameSql = $"ALTER TABLE \"main\".\"{entity.EDIT_TABLE_NAME}\" RENAME TO \"{oldname}\";";
                    //改自增
                    var autoin = string.Empty;
                    if (columns.Count(m => m.ISKEY == 1 && m.AUTOIN == 1) > 0)
                    {
                        var _autoin = conn.SqliteQueryDataTable($"select * from \"main\".\"sqlite_sequence\" where name = '{entity.TABLE_NAME}';");
                        if (_autoin.Rows.Count > 0)
                        {
                            autoin = $"INSERT INTO \"main\".\"sqlite_sequence\" (name, seq) VALUES ('{entity.TABLE_NAME}', '{_autoin.Rows[0][0]}');";
                        }
                    }
                    //复制内容 - 改前改后都有的字段
                    var _cols = from a in entity.Columns
                                join b in columns on a.EDIT_COLUMN_NAME equals b.COLUMN_NAME
                                select b.COLUMN_NAME;

                    var cols = string.Join(",", _cols.Select(m => $"\"{m}\""));
                    var insert = $"insert into \"main\".\"{entity.TABLE_NAME}\" ({cols}) SELECT {cols} FROM \"main\".\"{oldname}\";";

                    var bat = @$"{renameSql}{Environment.NewLine}{createTableSql}{Environment.NewLine}{autoin}{Environment.NewLine}{insert}";
                    conn.SqliteExcute(bat);
                }
            }
            catch (Exception ex)
            {
                return new() { flag = false, msg = ex.Message };
            }
            return new() { flag = true };
        }

        [HttpPost]
        public FlagEntity GetMetaData(GetMetaEntity entity)
        {
            using var conn = new SqliteConnection(entity.DbConnStr);

            var sql = conn.SqliteExcuteField(
                @$"SELECT SQL FROM sqlite_master WHERE name = '{entity.Name}'");

            var flag = !string.IsNullOrEmpty(sql);
            if (flag)
            {
                sql = $"drop view {entity.Name};\n\n{sql}";
            }

            return new FlagEntity
            {
                flag = flag,
                data = sql,
                msg = flag ? $"{entity.Name}加载成功" : "查询不到信息"
            };
        }

        [HttpPost]
        public object RunSql(RunSqlEntity entity)
        {
            using var conn = new SqliteConnection(entity.DbConnStr);

            var dts = conn.SqliteQueryDataTable(entity.Sqls, out string[] logs);

            var tables = new List<object>();
            if (dts?.Length > 0)
            {
                foreach (var dt in dts)
                {
                    var columns = new List<object>();
                    for (int i = 0; i < dt?.Columns.Count; i++)
                    {
                        var col = dt.Columns[i];
                        columns.Add(new { name = col.ColumnName, type = col.DataType.Name });
                    }
                    tables.Add(new { columns, data = dt });
                }
            }

            return new
            {
                flag = true,
                tables,
                logs
            };
        }
    }
}
